#-*- encoding: utf-8 -*-
import win32com.client 
from Misc.Utils import *

from DataAccess.QueryBlp import *
from DataAccess.DBConnFactory import DBConnFactory

from datetime import date,timedelta

from ReportTool.HldReport import *
from ReportTool.PnLReport import *
from ReportTool.NAVReport import *
from ReportTool.MktValReport import *
from ReportTool.TradeReport import *
from ReportTool.RealizedPnL import *
from ReportTool.OnshoreHldReport import *

from Config.ReportConfig import *

def QFII_TAX_ALLOCATION(ref_date):

	cursor = DBConnFactory().get_db_connection('ACCOUNTING').cursor()

	#realized_pnl = query_realized_pnl_average_cost(ref_date, 'SC028')
	realized_pnl = query_realized_pnl_first_in_first_out(ref_date, 'SC028')
	
	d = date(2007, 9, 30)
	QFII_TAX = []
	
	last_fx = 1
	upto_last_mth_tax_cny = 0
	
	sql_tpl = Template('''select child,shares,shares/sum_shares from
						share_account,		
							(select sum(shares) as sum_shares from share_account
								where parent = '${PARENT}' 
								and ref_date =TO_DATE('${DATE}','yyyy-mm-dd')-1)
						where ref_date = TO_DATE('${DATE}','yyyy-mm-dd')-1
							and parent = '${PARENT}'
							and shares<>0''')
							
							
	sql_tpl_feeder = Template('''select id, ref_date, parent, child, rmd_shares, rmd_shares/sum_shares  from
								(select sub.id, sub.ref_date, sub.parent, sub.child, sub.shares - NVL(red.sh,0) as rmd_shares from
								(select id, ref_date, parent, child, shares
												  from share_chg_ex
												 where type = 'subscription'
												 and ref_date < TO_DATE('${DATE}','yyyy-mm-dd')) sub
								left join 
								(select corr_id, sum(shares) as sh
													  from share_chg_ex
													 where type = 'redemption'
													 and ref_date < TO_DATE('${DATE}','yyyy-mm-dd')
													 and ref_date is not null
													 group by corr_id) red                    
								on red.corr_id = sub.id) rmd, 
								(select sum(shares) as sum_shares from share_account
								where parent = '${PARENT}'
								and ref_date =TO_DATE('${DATE}','yyyy-mm-dd')-1)
								where rmd_shares <> 0
								and parent = '${PARENT}' ''')
	
	while d <= ref_date:
		
		upto_crt_mth_tax_cny = 0
		#crt_fx = query_latest_fx(d, 'USD', 'CNY')
		crt_fx = 1
		
		for elem in realized_pnl:
			if elem[0] <= d and elem[2] == 'PNOTE' and elem[5] > 0 and elem[7] == 'REALIZED':
				upto_crt_mth_tax_cny += elem[5]*0.05
		
		crt_mth_tax_usd = upto_crt_mth_tax_cny/crt_fx - upto_last_mth_tax_cny/last_fx
			
		last_fx = crt_fx
		upto_last_mth_tax_cny = upto_crt_mth_tax_cny
		
		cursor.execute(sql_tpl.substitute(DATE = d.isoformat(), PARENT = 'SC028'))								  
		r = cursor.fetchall()
		
		for elem in r:
			feeder = elem[0]
			feeder_tax = round(crt_mth_tax_usd * elem[2], 2)
			cursor.execute(sql_tpl_feeder.substitute(DATE = d.isoformat(), PARENT = feeder))
			rst = cursor.fetchall()
			
			for each in rst:
				id = each[0]
				sub_date = each[1]
				parent = each[2]
				client = each[3]
				shares = each[4]
				client_tax = round(feeder_tax * each[5], 2)
				
				QFII_TAX.append((d, id, sub_date, parent, client, shares, client_tax))
				
		d = end_of_mth(d, 1)

	tplt_file = TPLT_PATH + 'QFII Tax Allocation.xls'
	xApp = win32com.client.Dispatch("Excel.Application")
	xBook = xApp.Workbooks.Open(tplt_file)
	xSht = xBook.Worksheets('Report')
	
	row = 2
	for elem in QFII_TAX:
	
		xSht.Range(xSht.Cells(row, 1), xSht.Cells(row, 7)).Value = elem
		row += 1
		
	rept_file = REPT_PATH + 'QFII Tax Allocation_' + ref_date.strftime("%Y%m%d") + '.xls'
	xBook.SaveAs(rept_file)
	xBook.Close()
	del xApp
	
	print 'info, QFII tax allocation report completed at ', ref_date.isoformat()